{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                     Call Id\n",
      "2015-01-01 09:00:00        4\n",
      "2015-01-01 10:00:00        4\n",
      "2015-01-01 11:00:00       10\n",
      "2015-01-01 12:00:00       12\n",
      "2015-01-01 13:00:00        8\n",
      "...                      ...\n",
      "2015-03-31 13:00:00        5\n",
      "2015-03-31 14:00:00        3\n",
      "2015-03-31 15:00:00        1\n",
      "2015-03-31 16:00:00        3\n",
      "2015-03-31 17:00:00        2\n",
      "\n",
      "[810 rows x 1 columns]\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "# 导入数据\n",
    "data=pd.read_excel('Call-Center-Dataset.xlsx')[['Call Id','Date']]\n",
    "# 将时间设为索引\n",
    "data.index=data['Date'].values\n",
    "del data['Date']\n",
    "# 按小时重采样\n",
    "data=data.groupby(data.index.to_period('H')).count()\n",
    "# 将索引变为时间序列\n",
    "data.index=data.index.astype('datetime64[ns]')\n",
    "# 填补缺失时间索引\n",
    "date = pd.date_range('2015-01-01 09',periods=9,freq='H')\n",
    "for day in pd.date_range('2015-01-02','2015-03-31',freq='D'):\n",
    "    date=date.union(pd.date_range(str(day)[:10]+' 09',periods=9,freq='H'))\n",
    "data=data.reindex(date)\n",
    "# 填补缺失值\n",
    "data=data.fillna(0)\n",
    "data['Call Id'] = data['Call Id'].astype(int)\n",
    "print(data)\n",
    "# data['Month']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2015-01-01 09:00:00     4\n",
      "2015-01-01 10:00:00     4\n",
      "2015-01-01 11:00:00    10\n",
      "2015-01-01 12:00:00    12\n",
      "2015-01-01 13:00:00     8\n",
      "                       ..\n",
      "2015-01-21 13:00:00     0\n",
      "2015-01-21 14:00:00     0\n",
      "2015-01-21 15:00:00     4\n",
      "2015-01-21 16:00:00     8\n",
      "2015-01-21 17:00:00    12\n",
      "Name: Call Id, Length: 189, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "# 划分测试集和训练集\n",
    "# t = data.truncate(before = '01/01/2015', after= '2015-01-22')['Call Id'].values\n",
    "# print(t)\n",
    "train=list(data.truncate(before='2015-01-01',after='2015-01-22')['Call Id'].values)\n",
    "test=list(data.truncate(before='2015-01-22')['Call Id'].values)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "\n",
    "# 参数\n",
    "s1=9\n",
    "s2=7*9\n",
    "alpha = 0.02\n",
    "gamma = 0.02\n",
    "delta = 0.01\n",
    "omega = 0.2\n",
    "phi = 0.8\n",
    "\n",
    "# 平滑项 趋势项 两个季节项\n",
    "St=[]\n",
    "Tt=[]\n",
    "Dt=[]\n",
    "Wt=[]\n",
    "y_hats=[]\n",
    "\n",
    "# 选取初值\n",
    "St.append(train[0])\n",
    "\n",
    "T0=(train[-1]-train[0])/(len(train)-1)\n",
    "Tt.append(T0)\n",
    "\n",
    "for i in range(s1):\n",
    "    si=train[i::s1]\n",
    "    Dt.append(np.mean(si))\n",
    "\n",
    "for i in range(s2):\n",
    "    si=train[i::s2]\n",
    "    Wt.append(np.mean(si))\n",
    "\n",
    "\n",
    "# 拟合模型\n",
    "for t in range(len(train)):\n",
    "    st=alpha*(train[t]-Dt[-s1]-Wt[-s2])+(1-alpha)*(St[-1]+Tt[-1])\n",
    "    St.append(st)\n",
    "\n",
    "    tt=gamma*(St[-1]-St[-2])+(1-gamma)*Tt[-1]\n",
    "    Tt.append(tt)\n",
    "\n",
    "    dt=delta*(train[t]-St[-1]-Wt[-s2])+(1-delta)*Dt[-s1]\n",
    "    Dt.append(dt)\n",
    "\n",
    "    wt=omega*(train[t]-St[-1]-Dt[-s1-1])+(1-omega)*Wt[-s2]\n",
    "    Wt.append(wt)\n",
    "\n",
    "    y_hat=St[-1]+Tt[-1]+Dt[-s1]+Wt[-s2]+phi*(train[t]-(St[-2]+Tt[-2]+Dt[-s1-1]+Wt[-s2-1]))\n",
    "    y_hats.append(y_hat)\n",
    "\n",
    "# 计算MSE\n",
    "MSE=np.mean((np.array(train)-np.array(y_hats))**2)\n",
    "MSE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 预测\n",
    "y_preds=[]\n",
    "for t in range(len(test)):\n",
    "    st=alpha*(y_hats[-1]-Dt[-s1]-Wt[-s2])+(1-alpha)*(St[-1]+Tt[-1])\n",
    "    St.append(st)\n",
    "\n",
    "    tt=gamma*(St[-1]-St[-2])+(1-gamma)*Tt[-1]\n",
    "    Tt.append(tt)\n",
    "\n",
    "    dt=delta*(y_hats[-1]-St[-1]-Wt[-s2])+(1-delta)*Dt[-s1]\n",
    "    Dt.append(dt)\n",
    "\n",
    "    wt=omega*(y_hats[-1]-St[-1]-Dt[-s1-1])+(1-omega)*Wt[-s2]\n",
    "    Wt.append(wt) \n",
    "\n",
    "    if t==0:\n",
    "        y_pred=St[-1]+Tt[-1]+Dt[-s1]+Wt[-s2]+phi*(train[-1]-(St[-2]+Tt[-2]+Dt[-s1-1]+Wt[-s2-1]))\n",
    "    else:\n",
    "        y_pred=St[-1]+Tt[-1]+Dt[-s1]+Wt[-s2]+phi*(y_preds[-1]-(St[-2]+Tt[-2]+Dt[-s1-1]+Wt[-s2-1]))\n",
    "    y_preds.append(y_pred)\n",
    "\n",
    "# 计算预测MSE\n",
    "MSE_hat=np.mean((np.array(test)-np.array(y_preds))**2)\n",
    "MSE_hat"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DatetimeIndex(['2018-01-01', '2018-01-02', '2018-01-03', '2018-01-04',\n",
      "               '2018-01-05', '2018-01-06', '2018-01-07', '2018-01-08',\n",
      "               '2018-01-09', '2018-01-10', '2018-01-11', '2018-01-12',\n",
      "               '2018-01-13', '2018-01-14', '2018-01-15', '2018-01-16',\n",
      "               '2018-01-17', '2018-01-18', '2018-01-19', '2018-01-20',\n",
      "               '2018-01-21'],\n",
      "              dtype='datetime64[ns]', freq='D')\n",
      "DatetimeIndex(['2009-01-23 00:00:00', '2009-01-23 01:00:00',\n",
      "               '2009-01-23 02:00:00', '2009-01-23 03:00:00',\n",
      "               '2009-01-23 04:00:00', '2009-01-23 05:00:00',\n",
      "               '2009-01-23 06:00:00', '2009-01-23 07:00:00',\n",
      "               '2009-01-23 08:00:00', '2009-01-23 09:00:00'],\n",
      "              dtype='datetime64[ns]', freq='H')\n",
      "DatetimeIndex(['2018-01-11', '2018-01-12', '2018-01-13', '2018-01-14',\n",
      "               '2018-01-15', '2018-01-16', '2018-01-17', '2018-01-18',\n",
      "               '2018-01-19', '2018-01-20'],\n",
      "              dtype='datetime64[ns]', freq='D')\n"
     ]
    }
   ],
   "source": [
    "t = pd.date_range(start='1/1/2018', end='1/21/2018', freq = 'D')\n",
    "print(t)\n",
    "t = pd.date_range(start = '2009-01-23', periods=10, freq = \"H\")\n",
    "print(t)\n",
    "t = pd.date_range(end = \"2018-01-20\", periods = 10)\n",
    "print(t)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 实现功能：\n",
    "- 从excel中读取了特定的列\n",
    "\n",
    "- 把时间序列数据<b>进行重采样</b>(原来是`DD/MM/YYYY HH:mm`的时间，需要重新采样变成`YYYY-MM-DD HH`的格式方便建模计算)\n",
    "\n",
    "- 直接将时间列设置成pandas 的index （这一步很nice，以前用pandas还没意识到index列丰富的数据清洗的API）\n",
    "\n",
    "- data_range 支持字符串拼接后自动补全一个period的时间序列数据，union来实现index 增补\n",
    "\n",
    "- 之所以需要增补是因为要将可能缺失的时间序列数据补全(比如可能在某个时间段(2022-01-21 09:00)没有电话呼入，此时这个时间段数据量为0，但是依然要设置一个时间序列索引来表示它)\n",
    "\n",
    "- 重标号，设置空值0\n",
    "\n",
    "- 最终结果会是`时间段 + 总Call数量`的一个表，有了清洗完的数据才能进行后续的数据分析\n",
    "\n",
    "- 这个数据集比较简单，后面会用ARIMA模型建模从而实现预测"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "     B\n",
      "1   ss\n",
      "2  sss\n",
      "3  ccc\n"
     ]
    }
   ],
   "source": [
    "a = [1,2,3]\n",
    "b = [\"ss\", \"sss\", \"ccc\"]\n",
    "df = pd.DataFrame({\"A\" : a, \"B\": b})\n",
    "df.index = df[\"A\"].values\n",
    "del df[\"A\"]\n",
    "df.index = df.index.astype(int)\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "data.to_csv(\"./result.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "da3096a4b2ddf6e3108983d9c4f8ed6b468b54d4f76a60924a980331cf3af984"
  },
  "kernelspec": {
   "display_name": "Python 3.8.13 ('py38')",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.13"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
